﻿/*****************************************************************************/
/* Stored Procedure :: SaveServiceMember                                     */
/*****************************************************************************/
/* Copyright (C) 2007, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller (edward.mueller@ps.net)                            */
/* Started:  10/07/2007                                                      */
/*****************************************************************************/
CREATE PROCEDURE [Kernel].[SaveServiceMember]
@OID			                bigint			= NULL,
@Version		                rowversion		= NULL,
@FirstName		                varchar(128)	= NULL,
@LastName		                varchar(128)	= NULL,
@MiddleInitial	                char(01)		= NULL,
@DOB			                smalldatetime	= NULL,
@Sex			                char(01)		= NULL,
@Phone1			                varchar(20)		= NULL,
@Phone2			                varchar(20)		= NULL,
@eMailAddress	                varchar(128)	= NULL,
@ServiceMenPrimaryAddressID     bigint          = NULL,
@ServiceMenPermanentAddressID   bigint          = NULL,
@ServiceHistoryOID		        bigint			= NULL,
@ServiceHistoryVersion	        rowversion		= NULL,
@BranchOfServiceID		        bigint			= NULL,
@RankID				            bigint			= NULL,
@UnitAssignment			        varchar(200)	= NULL,
@StartDate				        datetime		= NULL,
@EndDate				        datetime		= NULL,
@TypeOfDischargeOID		        bigint			= NULL,
@Active					        tinyint			= 1
AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: SaveServiceMember
--                                                                            
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

DECLARE @RetCode int,
        @RowCount int,
        @PersonVersion rowversion

SELECT @PersonVersion = NULL

-- 1. Save the fields associated with the person.
EXEC @RetCode = Kernel.SavePerson @OID OUTPUT, @PersonVersion OUTPUT, @FirstName, @LastName, @MiddleInitial, @DOB, @Sex, @Phone1, @Phone2, @eMailAddress
IF (@RetCode <> 0) Goto ExitError

-- 2. If new record (OID is not set)
IF (NOT EXISTS (SELECT * FROM dbo.ServiceMen WHERE (OID = @OID)))
BEGIN
	-- 2.1. Perform insert.
	INSERT INTO dbo.ServiceMen
	(OID, PrimaryAddressID, PermanentAddressID, UpdatedDate, CreatedDate)
	VALUES
	(@OID, @ServiceMenPrimaryAddressID, @ServiceMenPermanentAddressID, GETDATE(), GETDATE())
END

-- 3. If OID is set (existing record)
ELSE
BEGIN
	-- 3.1. Attempt updating object (using both OID and timestamp for concurrency reasons)
	UPDATE dbo.ServiceMen
	SET PrimaryAddressID = @ServiceMenPrimaryAddressID
	    ,PermanentAddressID = @ServiceMenPermanentAddressID
		,UpdatedDate = GETDATE()
	WHERE ((OID = @oid) AND (Version = @Version))
    SELECT @RetCode = @@ERROR, @RowCount = @@ROWCOUNT
    IF (@RetCode <> 0) GOTO ExitError

	-- 3.2. If no records were found (and updated) - it means that
	-- another process had updated this record (therefore timestamp will be different)
	-- or record was deleted (OID not found)
	-- Either way, we need to raise exception
	IF @RowCount = 0
	BEGIN
		RAISERROR(99999, 16, 1, 'ServiceMember' ) --WITH LOG
		GOTO ExitError
	END
END

---------------------------------------------------------------------------------------------------
-- Step 4: "Save" the ServiceHistory
---------------------------------------------------------------------------------------------------
-- Right now, there is no discharge information on the screen, so get it if it isn't passed in
IF ISNULL(@TypeOfDischargeOID, 0) <= 0
BEGIN
	SELECT @TypeOfDischargeOID = OID
	FROM dbo.TypesOfDischarge
	WHERE Description = 'N/A'
END

-- WARNING: For now, see if the service man has any history, update it.
SELECT @ServiceHistoryOID = OID, @ServiceHistoryVersion = Version
FROM dbo.ServiceHistory
WHERE (PersonnelID = @OID) AND
      (Version = (SELECT MAX(Version) FROM dbo.ServiceHistory
	              WHERE PersonnelID = @OID))

IF ISNULL(@ServiceHistoryOID, 0) <= 0 
BEGIN
	-- If no ServiceHistory OID was passed in, this is a new entry for that ServiceMan.
	INSERT INTO dbo.ServiceHistory
	(PersonnelID, BranchOfServiceID, RankID, StartDate, EndDate, 
	UnitAssignment, TypeOfDischargeID, Active, UpdatedDate, CreatedDate)
	VALUES(@OID, @BranchOfServiceID, @RankID, ISNULL(@StartDate, GETDATE()), @EndDate, 
	@UnitAssignment, @TypeOfDischargeOID, ISNULL(@Active, 1), GETDATE(), GETDATE())

	SET @ServiceHistoryOID =  SCOPE_IDENTITY()

	-- Set the OID and timestamp of newly inserted object
	SELECT @ServiceHistoryVersion = Version 
	FROM dbo.ServiceHistory 
	WHERE OID = @ServiceHistoryOID
END
ELSE
BEGIN
	-- ServiceHistory OID was supplied, so we will try to update the existing one
	-- Attempt updating object (using both OID and timestamp for concurrency reasons)
	UPDATE dbo.ServiceHistory
	SET BranchOfServiceID = @BranchOfServiceID
		,RankID = @RankID
		,StartDate = ISNULL(@StartDate, GETDATE())
		,EndDate = @EndDate
		,UnitAssignment = @UnitAssignment
		,TypeOfDischargeID = ISNULL(TypeOfDischargeID, 1)
		,Active = ISNULL(@Active, 1)
		,UpdatedDate = GETDATE()
	WHERE (OID = @ServiceHistoryOID) 
	AND (Version = @ServiceHistoryVersion)

	-- If no records were found (and updated) - it means that another process had updated 
	-- this record (therefore timestamp will be different) or record was deleted (OID not found)
	-- Either way, we need to raise exception
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR(99999, 16, 1, 'ServiceHistory')
		GOTO ExitError
	END

	-- Record was found - return OID and new timestamp
	ELSE
	BEGIN 
		SELECT @ServiceHistoryVersion = Version 
		FROM dbo.ServiceHistory 
		WHERE OID = @ServiceHistoryOID
	END
END

---------------------------------------------------------------------------------------------------
--	Return the values to the caller
---------------------------------------------------------------------------------------------------
SELECT OID, Version
FROM dbo.ServiceMen 
WHERE OID = @OID

RETURN(0)

ExitError:
	RETURN(1)


